# 数据库分析模块
class AnalyzeData:

    # 可筛选,日期时间,设备层数,分析对象,排序方式,分析整体错发汇总(重量段除外)
    # 需要传入的参数:
    #   start_date: 其实时间
    #   end_date: 结束时间
    #   floor: 设备层数
    #   field_name: 分析对象
    #   sorting: 设置排序方式
    def optional_date_floor_field_name_sorting_wronglysend(self, start_date, end_date, floor, field_name, sorting):
        sql = f"""select {field_name},count(distinct 运单号) 下错量,count(distinct 运单编号) 总下件量,
                         ifnull(round(count(distinct 运单号)/count(distinct 运单编号)*10000,2),0) 错发万分比
                  from
                  (select qm.运单编号,qm.分拣时间,qm.{field_name},gh.运单号
                  from
                  (select 运单编号,分拣时间,{field_name}
                  from assembly_line.奇门系统流水线数据
                  where 网点名称 = "深圳布吉"
                  and right(分拣时间,8) not between "06:00:00" and "17:00:00"
                  and 运单编号 not in(select 运单号
                                     from assembly_line.光合系统错发数据
                                     where 建包包号 in(select 建包包号
									                  from assembly_line.光合系统错发数据
									                  group by 建包包号,奖励网点
									                  having count(distinct 运单号) >= 5))
                  and 分拣时间 between "{start_date}" and "{end_date}"
                  and 建包时间 is not null
                  and 设备层数 = {floor}
                  and 分拣来源 = "正常读码") qm
                  left join assembly_line.光合系统错发数据 gh
                  on qm.运单编号 = gh.运单号) qg
                  group by {field_name}
                  order by {sorting} desc,{"下错量" if sorting == "错发万分比" else "错发万分比"} desc;"""
        return sql

    # 重量段
    def everyday_weightfor_floor_wronglysend(self, start_time, end_time, floor, field_name, sorting):
        sql = f"""select ifnull({field_name},"未知重量") {field_name},
                         count(distinct 运单号) 下错量,count(distinct 运单编号) 总下件量,
                         ifnull(round(count(distinct 运单号)/count(distinct 运单编号)*10000,2),0) 错发万分比
                  from
                  (select qg.运单编号,qg.分拣时间,qg.运单号,
                     (case
                         when cw.网点重量 > 0 and cw.网点重量 <=0.1 then "0.01-0.10"
                         when cw.网点重量 > 0.1 and cw.网点重量 <=0.2 then "0.11-0.20"
                         when cw.网点重量 > 0.2 and cw.网点重量 <=0.3 then "0.21-0.30"
                         when cw.网点重量 > 0.3 and cw.网点重量 <=0.4 then "0.31-0.40"
                         when cw.网点重量 > 0.4 and cw.网点重量 <=0.5 then "0.41-0.50"
                         when cw.网点重量 > 0.5 and cw.网点重量 <=0.6 then "0.51-0.60"
                         when cw.网点重量 > 0.6 and cw.网点重量 <=0.7 then "0.61-0.70"
                         when cw.网点重量 > 0.7 and cw.网点重量 <=0.8 then "0.71-0.80"
                         when cw.网点重量 > 0.8 and cw.网点重量 <=0.9 then "0.81-0.90"
                         when cw.网点重量 > 0.9 and cw.网点重量 <=1 then "0.91-1.00"
                         when cw.网点重量 > 1 then "大于1.00"
                     end) {field_name}
                  from
                  (select qm.运单编号,qm.分拣时间,gh.运单号
                  from
                  (select 运单编号,分拣时间
                  from assembly_line.奇门系统流水线数据
                  where 网点名称 = "深圳布吉"
                  and right(分拣时间,8) not between "06:00:00" and "17:00:00"
                  and 运单编号 not in(select 运单号
                                     from assembly_line.光合系统错发数据
                                     where 建包包号 in(select 建包包号
									                  from assembly_line.光合系统错发数据
									                  group by 建包包号,奖励网点
									                  having count(distinct 运单号) >= 5))
                  and 分拣时间 between "{start_time}" and "{end_time}"
                  and 建包时间 is not null
                  and 设备层数 = {floor}
                  and 分拣来源 = "正常读码") qm
                  left join assembly_line.光合系统错发数据 gh
                  on qm.运单编号 = gh.运单号) qg
                  left join (select distinct 运单号,网点重量
                             from assembly_line.财务系统重量数据) cw
                  on qg.运单编号 = cw.运单号) qgc
                  group by {field_name}
                  order by {sorting} desc,{"下错量" if sorting == "错发万分比" else "错发万分比"} desc;"""
        return sql

    # 可筛选,日期时间,分析对象,排序方式,分析整体错发汇总(重量段除外)
    # 需要传入的参数:
    #   start_date: 其实时间
    #   end_date: 结束时间
    #   field_name: 分析对象
    #   sorting: 设置排序方式
    def optional_date_field_name_sorting_wronglysend(self, start_date, end_date, field_name, sorting):
        sql = f"""select {field_name},count(distinct 运单号) 下错量,count(distinct 运单编号) 总下件量,
                         ifnull(round(count(distinct 运单号)/count(distinct 运单编号)*10000,2),0) 错发万分比
                  from
                  (select qm.运单编号,qm.分拣时间,qm.{field_name},gh.运单号
                  from
                  (select 运单编号,分拣时间,{field_name}
                  from assembly_line.奇门系统流水线数据
                  where 网点名称 = "深圳布吉"
                  and right(分拣时间,8) not between "06:00:00" and "17:00:00"
                  and 运单编号 not in(select 运单号
                                     from assembly_line.光合系统错发数据
                                     where 建包包号 in(select 建包包号
									                  from assembly_line.光合系统错发数据
									                  group by 建包包号,奖励网点
									                  having count(distinct 运单号) >= 5))
                  and 分拣时间 between "{start_date}" and "{end_date}"
                  and 建包时间 is not null
                  and 分拣来源 = "正常读码") qm
                  left join assembly_line.光合系统错发数据 gh
                  on qm.运单编号 = gh.运单号) qg
                  group by {field_name}
                  order by {sorting} desc,{"下错量" if sorting == "错发万分比" else "错发万分比"} desc;"""
        return sql

    # 重量段
    def everyday_weightfor_wronglysend(self, start_time, end_time, field_name, sorting):
        sql = f"""select ifnull({field_name},"未知重量") {field_name},
                         count(distinct 运单号) 下错量,count(distinct 运单编号) 总下件量,
                         ifnull(round(count(distinct 运单号)/count(distinct 运单编号)*10000,2),0) 错发万分比
                  from
                  (select qg.运单编号,qg.分拣时间,qg.运单号,
                     (case
                         when cw.网点重量 > 0 and cw.网点重量 <=0.1 then "0.01-0.10"
                         when cw.网点重量 > 0.1 and cw.网点重量 <=0.2 then "0.11-0.20"
                         when cw.网点重量 > 0.2 and cw.网点重量 <=0.3 then "0.21-0.30"
                         when cw.网点重量 > 0.3 and cw.网点重量 <=0.4 then "0.31-0.40"
                         when cw.网点重量 > 0.4 and cw.网点重量 <=0.5 then "0.41-0.50"
                         when cw.网点重量 > 0.5 and cw.网点重量 <=0.6 then "0.51-0.60"
                         when cw.网点重量 > 0.6 and cw.网点重量 <=0.7 then "0.61-0.70"
                         when cw.网点重量 > 0.7 and cw.网点重量 <=0.8 then "0.71-0.80"
                         when cw.网点重量 > 0.8 and cw.网点重量 <=0.9 then "0.81-0.90"
                         when cw.网点重量 > 0.9 and cw.网点重量 <=1 then "0.91-1.00"
                         when cw.网点重量 > 1 then "大于1.00"
                     end) {field_name}
                  from
                  (select qm.运单编号,qm.分拣时间,gh.运单号
                  from
                  (select 运单编号,分拣时间
                  from assembly_line.奇门系统流水线数据
                  where 网点名称 = "深圳布吉"
                  and right(分拣时间,8) not between "06:00:00" and "17:00:00"
                  and 运单编号 not in(select 运单号
                                     from assembly_line.光合系统错发数据
                                     where 建包包号 in(select 建包包号
									                  from assembly_line.光合系统错发数据
									                  group by 建包包号,奖励网点
									                  having count(distinct 运单号) >= 5))
                  and 分拣时间 between "{start_time}" and "{end_time}"
                  and 建包时间 is not null
                  and 分拣来源 = "正常读码") qm
                  left join assembly_line.光合系统错发数据 gh
                  on qm.运单编号 = gh.运单号) qg
                  left join (select distinct 运单号,网点重量
                             from assembly_line.财务系统重量数据) cw
                  on qg.运单编号 = cw.运单号) qgc
                  group by {field_name}
                  order by {sorting} desc,{"下错量" if sorting == "错发万分比" else "错发万分比"} desc;"""
        return sql


    # 查询整包错发 包号
    def query_packageid_allpackageerror(self, start_time, end_time):
        sql = f"""select 建包包号,错发日期
                  from assembly_line.光合系统错发数据
                  where 错发日期  between "{start_time}" and "{end_time}"
                  group by 建包包号,奖励网点
                  having count(distinct 运单号) >= 5
                  order by 错发日期;"""
        return sql

    # 查询整包错发 单号
    def query_wayid_allpackageerror(self, start_time, end_time):
        sql = f"""select 运单号,建包包号,错发日期
                  from assembly_line.光合系统错发数据
                  where 建包包号 in(select 建包包号
								   from assembly_line.光合系统错发数据
								   where 错发日期  between "{start_time}" and "{end_time}"
								   group by 建包包号,奖励网点
								   having count(distinct 运单号) >= 5)
				  order by 错发日期;"""
        return sql


    # 分拣口飘格情况分析
    def dispense_error_case(self, start_time, end_time):
        sql = f"""select 理论网点隔口,被错发网点隔口,count(distinct 运单号) 飘格数,
			             (case
                            when 理论网点隔口-被错发网点隔口>0 and 理论网点隔口-被错发网点隔口<=3 then "前倾"
                            when 理论网点隔口-被错发网点隔口<0 and 理论网点隔口-被错发网点隔口>=-3 then "后移"
                            when 理论网点隔口-被错发网点隔口>3 or 理论网点隔口-被错发网点隔口<-3 then "其他情况"
                          end) 飘格情况
                  from assembly_line.光合系统错发数据
                  where 错发日期 between "{start_time}" and "{end_time}"
                  and 运单号 not in(select 运单号
								   from assembly_line.光合系统错发数据
								   where 建包包号 in(select 建包包号
								   from assembly_line.光合系统错发数据
								   where 错发日期  between "2022-09-02 00:00:00" and "2022-09-17 00:00:00"
								   group by 建包包号,奖励网点
								   having count(distinct 运单号) >= 5))
                  and 运单号 not in(select 运单号
								   from assembly_line.光合系统错发数据
								   where 理论网点隔口= 被错发网点隔口
								   and 理论网点隔口 <> ""
								   and 被错发网点隔口 <> "")
                  and 理论网点隔口 <> ""
                  and 被错发网点隔口 <> ""						
                  group by 理论网点隔口,被错发网点隔口
                  order by 飘格数 desc;"""
        return sql

    # 飘格情况分析
    def sum_error_case(self, start_time, end_time):
        sql = f"""select 飘格情况,sum(一层飘格数) 一层飘格数,sum(二层飘格数) 二层飘格数,sum(一层飘格数)+sum(二层飘格数) 整体
                  from
                  (select 理论网点隔口,被错发网点隔口,sum(if(设备层数=1,0,1)) 一层飘格数,sum(if(设备层数=1,1,0)) 二层飘格数,
			        (case 
                        when 理论网点隔口-被错发网点隔口>0 and 理论网点隔口-被错发网点隔口<=3 then "前倾"
                        when 理论网点隔口-被错发网点隔口<0 and 理论网点隔口-被错发网点隔口>=-3 then "后移"
                        when 理论网点隔口-被错发网点隔口>3 or 理论网点隔口-被错发网点隔口<-3 then "其他情况"
                    end) 飘格情况
                  from assembly_line.光合系统错发数据
                  where 错发日期 between "{start_time}" and "{end_time}"
                  and 运单号 not in(select 运单号
								   from assembly_line.光合系统错发数据
								   where 建包包号 in(select 建包包号
												    from assembly_line.光合系统错发数据
													where 错发日期  between "{start_time}" and "{end_time}"
													group by 建包包号,奖励网点
													having count(distinct 运单号) >= 5))
                                   and 运单号 not in(select 运单号
								   from assembly_line.光合系统错发数据
								   where 理论网点隔口= 被错发网点隔口
								   and 理论网点隔口 <> ""
								   and 被错发网点隔口 <> "")
                  and 理论网点隔口 <> ""
                  and 被错发网点隔口 <> ""
                  group by 理论网点隔口,被错发网点隔口) a
                  group by 飘格情况 
                  order by 飘格情况 desc;"""
        return sql